﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using Fireball.Windows.Forms; // Where is located CodeEditorControl class
using Fireball.Syntax; // Where is located SyntaxDocument class
using Fireball.CodeEditor.SyntaxFiles;
using System.Data.SqlClient;
using System.IO; // Where is located commons SyntaxFiles

namespace DBLGen.UI.Design
{
	public partial class CustomSqlEditorForm : Form
	{
		public CustomSqlEditorForm(CustomAttributesQuery caq)
		{
			if (caq == null)
				throw new ArgumentNullException("caq");

			InitializeComponent();

			_customAttributesQuery = caq.Clone();
		}
		private CustomAttributesQuery _customAttributesQuery;
		public CustomAttributesQuery CustomAttributesQuery
		{
			get
			{
				return _customAttributesQuery;
			}
		}

		protected override void OnLoad(EventArgs e)
		{
			base.OnLoad(e);

			this.codeEditorControl1 = new CodeEditorControl();
			this.codeEditorControl1.Dock = DockStyle.Fill;
			this.codeEditorControl1.TabSize = 2;
			this.codeEditorControl1.Document = this.syntaxDocument1;
			CodeEditorSyntaxLoader.SetSyntax(this.codeEditorControl1, SyntaxLanguage.SqlServer2K5);

			this.codeEditorControl2 = new CodeEditorControl();
			this.codeEditorControl2.Dock = DockStyle.Fill;
			this.codeEditorControl2.TabSize = 2;
			this.codeEditorControl2.Document = this.syntaxDocument2;
			CodeEditorSyntaxLoader.SetSyntax(this.codeEditorControl2, SyntaxLanguage.XML);
			
			
			this.queryControl1.ColumnList = this._customAttributesQuery.GetTable().Columns;
			this.queryControl1.QueryRows.AddRange(this._customAttributesQuery.GetQueryRows());
			// this._customAttributesQuery.SQLDisplay -> contains last sql statement
			// this.ExecuteSql(this._customAttributesQuery.SQLDisplay);
			this.queryControl1.SetQueryRows();
			this.Loader();
		}

		protected override void OnClosing(CancelEventArgs e)
		{
			if (this.DialogResult == DialogResult.OK)
			{
				this.CustomAttributesQuery.SetQueryRows(this.queryControl1.QueryRows);
				this.CustomAttributesQuery.SQLDisplay = this.queryControl1.SqlQueryPreview;
			}			
			base.OnClosing(e);
		}

		void tabControl1_Selected(object sender, System.Windows.Forms.TabControlEventArgs e)
		{
			switch (e.TabPageIndex)
			{
				case 0:
					break;
				case 1:
					this.tabControl1.SelectTab(1);
					break;
				case 2:
					
					break;
				case 3:
					break;
				case 4:
				default:
					this.tabControl1.SelectTab(0);
					break;
			}
		}

		private void TxtGetQueryData_Click(object sender, EventArgs e)
		{
			try
			{
				this.Loader();
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, "this.Loader() failed.", MessageBoxButtons.OK, MessageBoxIcon.Error);	
			}
			
		}

		private void Loader()
		{
			this.GetSql();
			this.tabControl1.SelectTab(2);
			this.ExecuteSql(this.queryControl1.SqlQueryPreview);
		}

		public void DisplaySqlCodeEditor(string sqlQuery)
		{
			this.codeEditorControl1.Document.Text = sqlQuery;
		}

		private string GetSql()
		{
			this.queryControl1.BuildSQL();
			this.DisplaySqlCodeEditor(this.queryControl1.SqlQuery);
			
			string sqlStatement = this.queryControl1.SqlQuery;
			if (!string.IsNullOrEmpty(sqlStatement))
				this.tabControl1.SelectTab(1);

			return sqlStatement;
		}

		private void ExecuteSql(string query)
		{
			if (string.IsNullOrEmpty(query))
			{				
				return;
			}

			string dbConnectionString = this._customAttributesQuery.GetDbConnectionString();
			try
			{
				using (SqlConnection conn = new SqlConnection(dbConnectionString))
				{
					conn.Open();

					SqlCommand cmd = new SqlCommand(query, conn);
					using (SqlDataReader reader = cmd.ExecuteReader())
					{
						using (DataSet ds = new DataSet())
						{
							using (DataTable dt = new DataTable("CustomSql"))
							{
								ds.Tables.Add(dt);
								ds.Load(reader, LoadOption.OverwriteChanges, ds.Tables[0]);
								this.dataGridView2.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;

								this.dataGridView2.DataSource = ds.Tables[0];
								foreach (DataColumn col in ds.Tables[0].Columns)
								{
									col.ColumnMapping = MappingType.Attribute;
								}

								using (MemoryStream ms = new MemoryStream())
								{
									ds.Tables[0].WriteXml(ms, false);

									UTF8Encoding encoding = new UTF8Encoding();
									this.codeEditorControl2.Document.Clear();
									this.codeEditorControl2.Text = this.codeEditorControl2.Document.Text = string.Empty;
									string n = Encoding.UTF8.GetString(ms.ToArray());
									this.codeEditorControl2.Document.InsertText(n, 0, 0, false);
								}
							}
						}
						reader.Close();
					}
					conn.Close();
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, "SQL Execution Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}

		private void OnClickCloseAndSave(object sender, EventArgs e)
		{
			this.DialogResult = DialogResult.OK;
			this.Close();
		}
	}
}